<?php

class Application_Model_CustomerMapper {
	
	protected $_connection;
	
	public function __construct() {
		
		$this->_connection = new Application_Model_DbTable_Customer();
		
	}
	
	public function getCustomerById($partyNumber) {
		
		try {
			$sql = $this->_connection->select()
											->setIntegrityCheck(false)
											->from(array('c' => 'bis.tbl_customer'), array('customerNumber', 'partyNumber', 'customerName'))
											->joinLeft(array('cs' => 'bis.tbl_customer_salesrep'), 'cs.customer_partyNumber=c.partyNumber', null)
											->joinLeft(array('s' => 'bis.tbl_salesrep'), 's.id=cs.salesrep_id', array('salesRepId' => 'id'))
											->joinLeft(array('cm' => 'bis.tbl_customer_managementunit'), 'cm.customer_partyNumber = c.partyNumber', null)
											->joinLeft(array('m' => 'bis.tbl_managementUnit'), 'm.managementUnit = cm.managementUnit')
											->joinLeft(array('cp' => 'bis.tbl_customerpermissions'), 'cp.customer_partyNumber = c.partyNumber')
											->where('c.partyNumber='.$partyNumber);
			$result = $sql->query()->fetch();
		} catch (Exception $e) {
			echo $e->getMessage();
		}
		
		
		return $result;
	}
	
	
	public function getAll() {
		try {
			$sql= $this->_connection->select()
											->setIntegrityCheck(false)
											->from(array('c' => 'bis.tbl_customer'), array('customerNumber', 'partyNumber', 'customerName'))
											->joinLeft(array('cs' => 'bis.tbl_customer_salesrep'), 'cs.customer_partyNumber=c.partyNumber', null)
											->joinLeft(array('s' => 'bis.tbl_salesrep'), 's.id=cs.salesrep_id', 'salesRepName')
											->joinLeft(array('cm' => 'bis.tbl_customer_managementunit'), 'cm.customer_partyNumber = c.partyNumber', null)
											->joinLeft(array('m' => 'bis.tbl_managementUnit'), 'm.managementUnit = cm.managementUnit')
											->joinLeft(array('cp' => 'bis.tbl_customerpermissions'), 'cp.customer_partyNumber = c.partyNumber')
											->order('s.salesrepName DESC');
											
			$result = $sql->query()->fetchAll();
		} catch (Exception $e) {
			echo $e->getMessage();
		}
		
		$entries = array();
		
		//create & pass managementunit object
		foreach($result As $row) {
			//create managementUnit object
			$customer = new Application_Model_Customer();
				
			//fill managementUnit object from database result
			$customer->setCustomerName($row['customerName']);
			$customer->setCustomerNumber($row['customerNumber']);
			$customer->setPartyNumber($row['partyNumber']);
			$customer->setManagementUnit($row['managementUnit']);
			$customer->setSalesRepName($row['salesRepName']);
			$customer->setRmaPermission($row['rmaPermission']);
			$customer->setCampaignPermission($row['campaignPermission']);
				
			//add object to array which will be passsed on
			$entries[] = $customer;
		}
		return $entries;		
		
	}
	
	public function changeMapping($data) 
	{
		$sql_salesrep = 'INSERT INTO bis.tbl_customer_salesrep (customer_partyNumber, salesrep_id) VALUES ('.$data['partyNumber'].','.$data['salesRepId'].') ON DUPLICATE KEY UPDATE salesrep_id='.$data['salesRepId'];
		$sql_managementunit = 'INSERT INTO bis.tbl_customer_managementunit (customer_partyNumber, managementunit) VALUES ('.$data['partyNumber'].', \''.$data['managementUnit'].'\') ON DUPLICATE KEY UPDATE managementunit=\''.$data['managementUnit'].'\'';
		$sql_permissions = 'INSERT INTO bis.tbl_customerpermissions (customer_partyNumber, rmaPermission, campaignPermission) VALUES ('.$data['partyNumber'].', \''.$data['rmaPermission'].'\', \''.$data['campaignPermission'].'\') ON DUPLICATE KEY UPDATE rmaPermission=\''.$data['rmaPermission'].'\', campaignPermission=\''.$data['campaignPermission'].'\'';
		
		$this->_connection->getDefaultAdapter()->query($sql_salesrep)->execute();
		$this->_connection->getDefaultAdapter()->query($sql_managementunit)->execute();
		$this->_connection->getDefaultAdapter()->query($sql_permissions)->execute();
	}
	
	public function getCustomersForCampaign()
	{
		$sql = $this->_connection->select()
									->setIntegrityCheck(false)
									->from(array('c' => 'bis.tbl_customer'), array('c.partyNumber', 'c.customerName'))
									->joinLeft(array('cp' => 'tbl_customerpermissions'), 'cp.customer_partyNumber=c.partyNumber', null)
									->where('cp.campaignPermission=\'Y\'')
									->order('c.customerName');
		
		$result = Zend_Db_Table::getDefaultAdapter()->fetchPairs($sql);
		return $result;
	}
	
	public function getCustomersForRma()
	{
		$sql = $this->_connection->select()
									->setIntegrityCheck(false)
									->from(array('c' => 'bis.tbl_customer'), array('c.partyNumber', 'c.customerName'))
									->joinLeft(array('cp' => 'tbl_customerpermissions'), 'cp.customer_partyNumber=c.partyNumber', null)
									->where('cp.rmaPermission=\'Y\'')
									->order('c.customerName');
	
		$result = Zend_Db_Table::getDefaultAdapter()->fetchPairs($sql);
		return $result;
	}
	
	public function getOutletBySapcode($sapCode)
	{
		$sql = $this->_connection->select()
									->setIntegrityCheck(false)
									->from(array('m' => 'bis.tbl_mshData'), 'm.outletName')
									->where('m.status=\'A\' AND m.sapCode=\''.$sapCode.'\'');
		$result = $sql->query()->fetch();
		
		return $result;
	}
	
	
}